package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import person.Employee;
import person.Employee.Role;
import person.User;
import contract.EmployeeContract;
import controllers.ExceptionsTypes;

public class EmployeeContractWrapper {
	
	
	public static ArrayList<EmployeeContract> getAllEmployeeContracts(boolean closeConnection) throws Exception{
		ArrayList<EmployeeContract> empContracts = new ArrayList<EmployeeContract>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".employment_contract ";
	   
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    while (rs.next()) {
			EmployeeContract eCont = null;
        	long idEmpContract = rs.getLong("id_employment_contract");
        	String empTaxCode = rs.getString("employee_tax_code");
        	String role = rs.getString("role");
        	double wages = rs.getDouble("salary");
        	long startDate = rs.getLong("starting_date");
        	long endDate = rs.getLong("ending_date");
        	GregorianCalendar startingDate = new GregorianCalendar();
        	startingDate.setTimeInMillis(startDate);
        	GregorianCalendar endingDate = new GregorianCalendar();
        	endingDate.setTimeInMillis(endDate);
        	String clauses = rs.getString("terms");
    		Role ro = Role.valueOf(role);
        	eCont = new EmployeeContract(idEmpContract, startingDate,endingDate , clauses, ro, PersonWrapper.getPerson(empTaxCode, false), wages, rs.getBoolean("is_temporary"));
	    	empContracts.add(eCont);
			}
	    
	    stmt.close();
	    if(closeConnection)
	    	DBWrapper.getConnection().close();
	    return empContracts;
	}
	
	public static void insertEmpContract(EmployeeContract empCont, User resp, boolean closeConnection) throws SQLException, Exception{	
		Statement stmt = DBWrapper.getConnection().createStatement();
		Employee emp = new Employee(empCont.getPerson(), empCont.getRole(), resp.getName(), empCont.getPerson().getBirthDate(), empCont.getWages());
		try{
			EmployeeWrapper.getEmployee(emp.getTaxCode(), false);
		}catch(Exception ex){
			if(ex.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
				throw ex;
		}
		int isT=0;
		if(empCont.isTemporaryContract())isT=1;
		String query = "INSERT INTO " + DBWrapper.getDbname() + ".employment_contract " +
		               " (employee_tax_code,role,agency_responsable,salary,starting_date,ending_date, terms, is_temporary) " +
		               "VALUES('" + 
		               empCont.getPerson().getTaxCode() + "','" + 
		               empCont.getRole() +"','" + resp.getTaxCode()+ "',"+ 
		               empCont.getWages()  +", '" + 
		               empCont.getStipulationDate().getTimeInMillis() +"', '"+ 
		               empCont.getValidityDate().getTimeInMillis() + "', '" + 
		               empCont.getClauses() +"', '" +
		               isT +"')";
		
		int rows = 0;
		rows = stmt.executeUpdate(query);
		
		if(rows == 0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
			stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	}
	
	public static EmployeeContract getEmployeeContract(long idContract, boolean closeConnection) throws Exception{
		EmployeeContract eCont = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".employment_contract " +
	    		"WHERE id_employment_contract = "+idContract;
	    Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if(rs.next()){
        	String empTaxCode = rs.getString("employee_tax_code");
        	String role = rs.getString("role");
        	double wages = rs.getDouble("salary");
        	long startDate = rs.getLong("starting_date");
        	long endDate = rs.getLong("ending_date");
        	GregorianCalendar startingDate = new GregorianCalendar();
        	startingDate.setTimeInMillis(startDate);
        	GregorianCalendar endingDate = new GregorianCalendar();
        	endingDate.setTimeInMillis(endDate);
        	String clauses = rs.getString("terms");
    		Role ro = Role.valueOf(role);
        	eCont = new EmployeeContract(idContract, startingDate,endingDate , clauses, ro, PersonWrapper.getPerson(empTaxCode, false), wages, rs.getBoolean("is_temporary"));
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
    if(eCont == null)
    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
    return eCont;
	}
	
	public static EmployeeContract getEmployeeContract(Employee emp, boolean closeConnection) throws Exception{
		EmployeeContract eCont = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".employment_contract " +
	    		"WHERE employee_tax_code = '"+ emp.getTaxCode() +"'";
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long idEmpContract = rs.getLong("id_employment_contract");
	        	String empTaxCode = rs.getString("employee_tax_code");
	        	String role = rs.getString("role");
	        	double wages = rs.getDouble("salary");
	        	long startDate = rs.getLong("starting_date");
	        	long endDate = rs.getLong("ending_date");
	        	GregorianCalendar startingDate = new GregorianCalendar();
	        	startingDate.setTimeInMillis(startDate);
	        	GregorianCalendar endingDate = new GregorianCalendar();
	        	endingDate.setTimeInMillis(endDate);
	        	String clauses = rs.getString("terms");
        		Role ro = Role.valueOf(role);
	        	eCont = new EmployeeContract(idEmpContract, startingDate,endingDate , clauses, ro, PersonWrapper.getPerson(empTaxCode, false), wages, rs.getBoolean("is_temporary"));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    if(eCont == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return eCont;
	}
	
public static void deleteEmployeeContract(EmployeeContract empCont, boolean closeConnection) throws Exception{
		
		String query =  "DELETE "  +
		    			"FROM "+ DBWrapper.getDbname() +".employment_contract " +
		    			"WHERE id_employment_contract="+ empCont.getId();
		    
		    	Statement stmt = DBWrapper.getConnection().createStatement();
		        int rows = stmt.executeUpdate(query);
		        if(rows>1){
		        	throw new SQLException();
		        }else if(rows == 0)
		    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		        stmt.close();
		        if(closeConnection)
		        	DBWrapper.getConnection().close();
	}	

	public static void modifyEmployeeContract(EmployeeContract empCont, boolean closeConnection) throws Exception{
	
	Statement stmt = DBWrapper.getConnection().createStatement();
	
	EmployeeContractWrapper.getEmployeeContract(empCont.getId(), closeConnection);
	
	int rows = stmt.executeUpdate(
	        "UPDATE "+ DBWrapper.getDbname() +".employment_contract" +
	        " SET role='" + empCont.getRole().toString() +
	        "', salary="+ empCont.getWages() +
	        ", starting_date=" + empCont.getStipulationDate().getTimeInMillis() +
	        ", ending_date=" + empCont.getValidityDate().getTimeInMillis() +
	        ", terms='"+empCont.getClauses() +
	        ", is_temporary='" + empCont.isTemporaryContract() + 
	        "' WHERE id_contract=" + empCont.getId());
	if(rows == 0)
		throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		stmt.close();
		
		if(closeConnection)
			DBWrapper.getConnection().close();
}


}
